#include "pmdatebaseoperations.h"
#include <QDebug>
#include "common.h"
#include "tabledefine.h"

QSqlDatabase PMDB;
QStringList oneProjectInfo;

bool connectPMDB(QString dbName)
{
    if (QSqlDatabase::contains(PMDB_CONNECTION))
    {
        PMDB = QSqlDatabase::database(PMDB_CONNECTION);
    }
    else
    {
        PMDB = QSqlDatabase::addDatabase("QSQLITE", PMDB_CONNECTION);
        PMDB.setDatabaseName(dbName);
        PMDB.setUserName("yanshang");
        PMDB.setPassword("912");
    }
    if(!PMDB.open())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<PMDB.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"连接并打开PM数据库成功";
    return true;
}

void closePMDB()
{
    if(!PMDB.isOpen())
        PMDB.close();
}

void removePMDB()
{
    if (QSqlDatabase::contains(PMDB_CONNECTION))
        QSqlDatabase::removeDatabase(PMDB_CONNECTION);
}

bool initPMDB()
{
    QSqlQuery query(PMDB);
    QStringList tables = PMDB.tables();
    QString sql = QString("create table %1 (%2 int primary key, "
                  "%3 VARCHAR(200), "
                  "%4 DateTime,"
                  "%5 DateTime,"
                  "%6 int)").arg(PM_TABLE_NAME, PROJECT_ID, PROJECT_NAME, 
                                      PROJECT_CREATE_DATE_TIME, PROJECT_LAST_CHANGE_DATE_TIME, FLAG);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<PMDB.lastError();
            return false;
        }
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"数据库表格已存在";
    
    sql = QString("update %1 set %2 = %3").arg(PM_TABLE_NAME, FLAG, _FLAG);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    query.prepare(sql);
    query.bindValue(_FLAG, DEFAULT_FLAG);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"初始化flag失败"<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"初始化flag成功";
    return true;
}

int getNextProjectID()
{
    QSqlQuery query(PMDB);
    QString sql = QString("select max(%1) from %2").arg(PROJECT_ID, PM_TABLE_NAME);
    if(!query.exec(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取数据库项目最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

bool addProject(int projectID, QString projectName, 
                                      QString projectCreateDateTime, QString projectLastChangeDateTime)
{
    QSqlQuery query(PMDB);
    QString sql = QString("select %1 from %2").arg(PROJECT_NAME, PM_TABLE_NAME);
    if(!query.exec(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    while(query.next())
    {
        if(query.value(0).toString() == projectName)
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"项目名称已存在"<<query.value(0).toString();
            QString tmp = QString("(%1)").arg(projectID - 1);
            projectName.append(tmp);
        }
    }
    
    sql = QString("insert into %1 values (?, ?, ?, ?, ?)").arg(PM_TABLE_NAME);
    
    if(!query.prepare(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(projectID);
    query.addBindValue(projectName);
    query.addBindValue(projectCreateDateTime);
    query.addBindValue(projectLastChangeDateTime);
    query.addBindValue(DEFAULT_FLAG);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    return true;
}

bool deleteProject(int projectID)
{
    QSqlQuery query(PMDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(PM_TABLE_NAME, PROJECT_ID);
    
    query.prepare(sql);
    query.addBindValue(projectID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool updateProjectName(int projectID, QString projectName)
{
    QSqlQuery query(PMDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(PM_TABLE_NAME, 
                                                                     PROJECT_NAME, _PROJECT_NAME, 
                                                                     PROJECT_ID, _PROJECT_ID);
    
    query.prepare(sql);
    query.bindValue(_PROJECT_NAME, projectName);
    query.bindValue(_PROJECT_ID, projectID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新项目名称成功";
    return true;
}

bool updateProjectLastChangeDateTime(int projectID, QString lastChangeDateTime)
{
    QSqlQuery query(PMDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(PM_TABLE_NAME, 
                                                                     PROJECT_LAST_CHANGE_DATE_TIME, 
                                                                     _PROJECT_LAST_CHANGE_DATE_TIME
                                                                     PROJECT_ID, _PROJECT_ID);
    
    query.prepare(sql);
    query.bindValue(_PROJECT_LAST_CHANGE_DATE_TIME, lastChangeDateTime);
    query.bindValue(_PROJECT_ID, projectID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新项目最终修改时间成功";
    return true;
}

bool updateProjectFlagByID(int projectID, int flagValue)
{
    QSqlQuery query(PMDB);
    QString sql = QString("update %1 set %2 = %3").arg(PM_TABLE_NAME, FLAG, _FLAG);
    
    query.prepare(sql);
    query.bindValue(_FLAG, DEFAULT_FLAG);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    
    sql = QString("update %1 set %2 = %3 where %4 = %5").arg(PM_TABLE_NAME, 
                                                                     FLAG, _FLAG, 
                                                                     PROJECT_ID, _PROJECT_ID);
    query.prepare(sql);
    query.bindValue(_FLAG, flagValue);
    query.bindValue(_PROJECT_ID, projectID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新flag成功";
    return true;
}

bool getOneProjectInfo(int projectID)
{
    QSqlQuery query(PMDB);
    QString sql = QString("select %1,%2,%3 from %4 where %5 = %6").arg(PROJECT_NAME, 
                                                                       PROJECT_CREATE_DATE_TIME, 
                                                                       PROJECT_LAST_CHANGE_DATE_TIME, 
                                                                       PM_TABLE_NAME, 
                                                                       PROJECT_ID, 
                                                                       _PROJECT_ID);
    
    query.prepare(sql);
    query.bindValue(_PROJECT_ID, projectID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    if(query.first())
    {
        oneProjectInfo.clear();
        oneProjectInfo.append(query.value(0).toString());
        oneProjectInfo.append(query.value(1).toString());
        oneProjectInfo.append(query.value(2).toString());
        return true;
    }
    return false;
}

int getProjectIDByFlag(int falgValue)
{
    QSqlQuery query(PMDB);
    QString sql = QString("select %1 from %2 where %3 = %4").arg(PROJECT_ID, PM_TABLE_NAME,
                                                                 FLAG, _FLAG);
    
    query.prepare(sql);
    query.bindValue(_FLAG, falgValue);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取到的项目id为:"<<query.value(0).toInt();
        return query.value(0).toInt();
    }
    return -2;
}
